package il.ac.braude.sona.ocsf.server;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Timer;
import java.util.TimerTask;
/**
 * A class for an agent that will check the status of each execution stage and will
 * identify exceptions in stage overdue and will initialize a message process for a reminder about 1 day overdue for each 
 * leader of the stage that it's execution time is about to end
 * the agent will run once a day and every time we open the server to check for this anomalies and will insert values into database
 * @author <p>SONA, Sasha,Ofek,Nitzan and Avihai, Group 6,<br>Laboratory in Software Engineering,Ort Braude College.</p>
 */
public class ScheduleAgent extends TimerTask {
	/**
	 * variable for timing procedures 
	 */
	private Timer timer = new Timer();
	/**
	 * static instance of the agent
	 */
	private static ScheduleAgent instance;
	private static final int day = 1000*60*60*24;
	/**
	 * activates the agent service
	 */
	public static void activate() {
		if (instance==null) instance=new ScheduleAgent();
	}
	/**
	 * close the agent and deactivate the service
	 */
	public static void deactivate() {
		if (instance==null) return;
		instance.cancel();
		instance.timer.cancel();
		instance=null;
		
	}
	/**
	 * sets timing of the agent
	 */
	public ScheduleAgent() {
		timer.schedule(this, 5000, day);
	}
	
	@Override
	public void run() {
		Connection con = null;
		
		try {
			con = DatabaseController.getConnection();
			HashSet<String> regularRecievers = new HashSet<String>();
			try {
				ArrayList<HashMap<String, String>> results = SQLHelper.select("users", "tid='manager' OR tid='supervisor'", con, "uid");
				for (HashMap<String, String> hash : results) {
					regularRecievers.add(hash.get("uid"));
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			String wtfQuery = "select eid,rid,uid1,uid2,uid3 from " +
			"(select st.eid as eid,st.rid as rid, case st.sid " +
			"when 'Board review' then (select uid from users where tid='b.chairman') " +
			"else (select uid from worker_type_in_request w ,type_status t where st.sid=t.sid and t.tid=w.tid and st.rid=w.rid )" +
			" end as uid1 , case st.sid " +
			"when 'Board review' then (select  uid from users where tid='b.member'  ORDER BY uid DESC LIMIT 0,1) end as uid2, " +
			"case st.sid when 'Board review' then (select  uid from users where tid='b.member' ORDER BY uid ASC LIMIT 0,1) end as uid3 " +
			"from status_execution st) as handlers where eid in (" +
			"select eid " +
			"from status_execution " +
			"where ADDDATE(date_started, INTERVAL estimate_time+extension_time DAY)<CURRENT_DATE and (date_finished is NULL or ADDDATE(date_started, INTERVAL estimate_time+extension_time DAY)<date_finished));";
			ResultSet res = con.prepareStatement(wtfQuery).executeQuery();
			if (res==null) return;
			while (res.next()) {
				HashSet<String> whoToSendTo = new HashSet<String>(regularRecievers);
				whoToSendTo.add(res.getString("uid1"));
				whoToSendTo.add(res.getString("uid2"));
				whoToSendTo.add(res.getString("uid3"));
				for (String uid : whoToSendTo) 
					if (uid!=null) 
						notifyException(con,res.getString("eid"),res.getString("rid"),uid);
			}
			String wtfQuery2 = "select eid,rid,uid1,uid2,uid3 from " +
			"(select st.eid as eid,st.rid as rid, case st.sid " +
			"when 'Board review' then (select uid from users where tid='b.chairman') " +
			"else (select uid from worker_type_in_request w ,type_status t where st.sid=t.sid and t.tid=w.tid and st.rid=w.rid )" +
			" end as uid1 , case st.sid " +
			"when 'Board review' then (select  uid from users where tid='b.member'  ORDER BY uid DESC LIMIT 0,1) end as uid2, " +
			"case st.sid when 'Board review' then (select  uid from users where tid='b.member' ORDER BY uid ASC LIMIT 0,1) end as uid3 " +
			"from status_execution st) as handlers where eid in (" +
			"select eid " +
			"from status_execution " +
			"where ADDDATE(date_started, INTERVAL estimate_time+extension_time DAY)>CURRENT_DATE AND date_finished is NULL AND  CURRENT_DATE>=ADDDATE(date_started, INTERVAL estimate_time+extension_time-1 DAY));";
			res = con.prepareStatement(wtfQuery2).executeQuery();
			if (res==null) return;
			while (res.next()) {
				HashSet<String> whoToSendTo = new HashSet<String>();
				whoToSendTo.add(res.getString("uid1"));
				whoToSendTo.add(res.getString("uid2"));
				whoToSendTo.add(res.getString("uid3"));
				for (String uid : whoToSendTo) 
					if (uid!=null){ 
						MessegesAPI.sendReminderMessege(con,res.getString("eid"),res.getString("rid"),uid);
						EmailSenderAPI.sendReminderToWorker(uid,res.getString("rid"));
					}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (con!=null)
				try {
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
		
		
	}
	/**
	 * inserts the stage exception into database and notifies the stage leader and the manager about the exception
	 * @param con - the client connection
	 * @param executionid  - stage execution id
	 * @param rid          - request id
	 * @param uid          - stage leader id
	 */
	private void notifyException(Connection con,String executionid,String rid,String uid) {
		try {SQLHelper.insert(con, "exceptions",new String[]{ "executionid" }, new String[]{ executionid });}catch(SQLException e){}
		MessegesAPI.sendExceptionMessege(uid,rid, executionid);
		
	}
	

}
